/*	Author: Dao Anh Vu
 *  This is DAO for Account and AccountType
 * */

package nautilus.moneykeeper.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import nautilus.moneykeeper.dto.Account;
import nautilus.moneykeeper.dto.AccountType;
import nautilus.moneykeeper.dto.Category;
import nautilus.moneykeeper.dto.CurrencyUnit;
import nautilus.moneykeeper.dto.PaymentMethod;
import nautilus.moneykeeper.dto.Transaction;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class AccountDAO{
	private SQLiteDatabase _database;
	private MoneyDataHelper _helper;
	//private SimpleDateFormat _format = new SimpleDateFormat("dd:MM:yyyy hh:mm:ss", SystemConfig.LOCALE);
	private SimpleDateFormat _format = new SimpleDateFormat("dd:MM:yyyy hh:mm:ss");
	
	public AccountDAO(Context context){
		_helper = new MoneyDataHelper(context);
		_database = _helper.getWritableDatabase();
		//Log.i(TAG, "Create Helper and get Database OK");
	}
	
	public AccountDAO(MoneyDataHelper helper){
		_helper = helper;
		_database = _helper.getWritableDatabase();
	}
	
	public void close(){
		_helper.close();
	}
	
	@Override
	public void finalize(){
		if(_helper != null)
			_helper.close();
	}
	/*==============================Operations for Category==========================================*/
	public void addCategory(Category cat){
		ContentValues values = new ContentValues();
		values.put("_name", cat.getName());
		values.put("_direction", cat.getDirection());
		values.put("_active", cat.isActive()?1:0);
		long id = _database.insert(MoneyDataHelper.CATEGORY_TABLE, null, values);
		cat.setId(id);
		//Log.i(TAG, String.format("Insert AccountType(%d, %s) into database successfully!", cat.getId(), cat.getName()));
	}
	
	public Category getCategoryById(long id){
		Category cat;
		Cursor cursor = _database.query(MoneyDataHelper.CATEGORY_TABLE, 
				new String[]{"_id", "_name", "_direction", "_active"}, 
				"_id=?", 
				new String[]{String.valueOf(id)}, 
				null, null, null);
		cursor.moveToFirst();
		
		cat = new Category();
		cat.setId(cursor.getLong(0));
		cat.setName(cursor.getString(1));
		cat.setDirection(cursor.getInt(2));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return cat;
	}
	
	public ArrayList<Category> getAllCategories(){
		ArrayList<Category> accs = new ArrayList<Category>();
		Category cat;
		Cursor cursor = _database.query(MoneyDataHelper.CATEGORY_TABLE,
				new String[]{"_id", "_name", "_direction"}, null, null, null, null, null);
		cursor.moveToFirst();
		
		while(!cursor.isAfterLast()){
			cat = new Category();
			cat.setId(cursor.getLong(0));
			cat.setName(cursor.getString(1));
			cat.setDirection(cursor.getInt(2));
			accs.add(cat);
			cursor.moveToNext();
			//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", cat.getId(), cat.getName()));
		}
		cursor.close();
		return accs;
	}
	
	public Category getCategoryFromList(List<Category> cats, long id){
		
		for(Category ac: cats){
			if(ac.getId() == id)
				return ac;
		}
		Category acc = getCategoryById(id);
		return acc;
	}
	
	/*==============================Operations for AccountType==========================================*/
	public void addAccountType(AccountType acct){
		ContentValues values = new ContentValues();
		values.put("_name", acct.getName());
		long id = _database.insert(MoneyDataHelper.ACCOUNTTYPE_TABLE, null, values);
		acct.setId(id);
		//Log.i(TAG, String.format("Insert AccountType(%d, %s) into database successfully!", acct.getId(), acct.getName()));
	}
	
	public AccountType getAccountTypeById(long id){
		AccountType acc;
		Cursor cursor = _database.query(MoneyDataHelper.ACCOUNTTYPE_TABLE, 
				MoneyDataHelper.columns_accounttype, 
				"_id=?", 
				new String[]{String.valueOf(id)}, 
				null, null, null);
		cursor.moveToFirst();
		
		acc = new AccountType();
		acc.setId(cursor.getLong(0));
		acc.setName(cursor.getString(1));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return acc;
	}
	
	public ArrayList<AccountType> getAllAccountTypes(){
		ArrayList<AccountType> accs = new ArrayList<AccountType>();
		AccountType acc;
		Cursor cursor = _database.query(MoneyDataHelper.ACCOUNTTYPE_TABLE, 
				MoneyDataHelper.columns_accounttype, null, null, null, null, null);
		cursor.moveToFirst();
		
		while(!cursor.isAfterLast()){
			acc = new AccountType();
			acc.setId(cursor.getLong(0));
			acc.setName(cursor.getString(1));
			accs.add(acc);
			cursor.moveToNext();
			//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		}
		cursor.close();
		return accs;
	}
	
	public Account getAccountFromList(List<Account> accs, long id){
		
		for(Account ac: accs){
			if(ac.getId() == id)
				return ac;
		}
		Account acc = getAccountById(id);
		return acc;
	}
	/*===========================================================================================*/
	public void addCurrencyUnit(CurrencyUnit unit){
		ContentValues values = new ContentValues();
		values.put("_name", unit.getName());
		values.put("_rate", unit.getRate());
		values.put("_nation", unit.getNation());
		long id = _database.insert(MoneyDataHelper.CURRENCYUNIT_TABLE, null, values);
		unit.setId(id);
	}
	
	public CurrencyUnit getCurrencyUnitByName(String strUnit){
		CurrencyUnit unit;
		Cursor cursor = _database.query(MoneyDataHelper.CURRENCYUNIT_TABLE, 
				new String[]{"_id", "_name", "_rate", "_nation"}, 
				"_name=?", 
				new String[]{strUnit}, 
				null, null, null);
		cursor.moveToFirst();
		
		unit = new CurrencyUnit();
		unit.setId(cursor.getLong(0));
		unit.setName(cursor.getString(1));
		unit.setRate(cursor.getFloat(2));
		unit.setNation(cursor.getString(3));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return unit;
	}
	
	public CurrencyUnit getCurrencyUnitById(int id){
		CurrencyUnit unit;
		Cursor cursor = _database.query(MoneyDataHelper.CURRENCYUNIT_TABLE, 
				new String[]{"_id", "_name", "_rate", "_nation"}, 
				"_id=?", 
				new String[]{id+""},
				null, null, null);
		cursor.moveToFirst();
		
		unit = new CurrencyUnit();
		unit.setId(cursor.getLong(0));
		unit.setName(cursor.getString(1));
		unit.setRate(cursor.getFloat(2));
		unit.setNation(cursor.getString(3));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return unit;
	}
	
	public ArrayList<CurrencyUnit> getAllCurrencyUnits(){
		ArrayList<CurrencyUnit> accs = new ArrayList<CurrencyUnit>();
		CurrencyUnit unit;
		Cursor cursor = _database.query(MoneyDataHelper.CURRENCYUNIT_TABLE,
				new String[]{"_id", "_name", "_rate", "_nation"},
				null, null, null, null, null);
		cursor.moveToFirst();
		
		while(!cursor.isAfterLast()){
			unit = new CurrencyUnit();
			unit.setId(cursor.getLong(0));
			unit.setName(cursor.getString(1));
			unit.setRate(cursor.getFloat(2));
			unit.setNation(cursor.getString(3));
			accs.add(unit);
			cursor.moveToNext();
		}
		cursor.close();
		return accs;
	}
	
	/*===========================================================================================*/
	public void addPaymentMethod(PaymentMethod pm){
		ContentValues values = new ContentValues();
		values.put("_name", pm.getName());
		long id = _database.insert(MoneyDataHelper.PAYMENTMETHOD_TABLE, null, values);
		pm.setId(id);
		//Log.i(TAG, String.format("Insert AccountType(%d, %s) into database successfully!", pm.getId(), pm.getName()));
	}
	
	public PaymentMethod getPaymentMethodById(long id){
		PaymentMethod pm;
		Cursor cursor = _database.query(MoneyDataHelper.PAYMENTMETHOD_TABLE, 
				new String[]{"_id", "_name"}, 
				"_id=?", 
				new String[]{String.valueOf(id)}, 
				null, null, null);
		cursor.moveToFirst();
		
		pm = new PaymentMethod();
		pm.setId(cursor.getLong(0));
		pm.setName(cursor.getString(1));
		cursor.close();
		return pm;
	}
	
	public ArrayList<PaymentMethod> getAllPaymentMethods(){
		ArrayList<PaymentMethod> accs = new ArrayList<PaymentMethod>();
		PaymentMethod acc;
		Cursor cursor = _database.query(MoneyDataHelper.PAYMENTMETHOD_TABLE, 
				new String[]{"_id", "_name"}, null, null, null, null, null);
		cursor.moveToFirst();
		
		while(!cursor.isAfterLast()){
			acc = new PaymentMethod();
			acc.setId(cursor.getLong(0));
			acc.setName(cursor.getString(1));
			accs.add(acc);
			cursor.moveToNext();			
		}
		cursor.close();
		return accs;
	}
	/*===========================================================================================*/
	
	public void addAccount(Account acc){
		ContentValues values = new ContentValues();
		values.put("_number", acc.getId());
		values.put("_name", acc.getName());
		values.put("_balance", acc.getBalance());
		values.put("_accountType", acc.getType().getId());
		values.put("_unit", acc.getCurrencyUnit().getId());
		values.put("_datetime", _format.format(acc.getDateTime()));
		values.put("_desc", acc.getDescription());
		
		long id = _database.insert(MoneyDataHelper.ACCOUNT_TABLE, null, values);
		acc.setId(id);
		//Log.i(TAG, String.format("Insert account(%d, %s) into database successfully!", acc.getId(), acc.getName()));
	}
	
	public void updateAccount(Account acc){
		ContentValues values = new ContentValues();		
		values.put("_number", acc.getNumber());
		values.put("_name", acc.getName());
		values.put("_accountType", acc.getType().getId());
		values.put("_unit", acc.getCurrencyUnit().getId());
		values.put("_balance", acc.getBalance());
		values.put("_datetime", _format.format(acc.getDateTime()));
		values.put("_desc", acc.getDescription());
		_database.update(MoneyDataHelper.ACCOUNT_TABLE, values, "_id=?", new String[]{acc.getId()+""});
	}
	
	public Account getAccountById(long id){
		Account acc;
		Cursor cursor = _database.query(MoneyDataHelper.ACCOUNT_TABLE,
				MoneyDataHelper.columns_account, 
				"_id=?", 
				new String[]{String.valueOf(id)}, null, null, null);
		cursor.moveToFirst();
		
		acc = new Account();
		acc.setId(cursor.getLong(0));
		acc.setNumber(cursor.getString(1));
		acc.setName(cursor.getString(2));			
		acc.setCurrencyUnit(getCurrencyUnitById(cursor.getInt(3)));
		acc.setType(getAccountTypeById(cursor.getLong(4)));
		acc.setBalance(cursor.getLong(5));
		try {
			Date date = _format.parse(cursor.getString(6));
			acc.setDateTime(date);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		acc.setDescription(cursor.getString(7));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return acc;
	}
	
	public ArrayList<Account> getAllAccounts(){
		ArrayList<Account> accs = new ArrayList<Account>();
		Account acc;
		Cursor cursor = _database.query(MoneyDataHelper.ACCOUNT_TABLE, MoneyDataHelper.columns_account, 
				null, null, null, null, null);
		cursor.moveToFirst();
		
		ArrayList<CurrencyUnit> units = new ArrayList<CurrencyUnit>();
		ArrayList<AccountType> types = new ArrayList<AccountType>();
		while(!cursor.isAfterLast()){
			acc = new Account();
			acc.setId(cursor.getLong(0));
			acc.setNumber(cursor.getString(1));
			acc.setName(cursor.getString(2));			
			acc.setCurrencyUnit((getCurrencyUnitFromList(units, cursor.getInt(3))));
			acc.setType(getAccountTypeFromList(types, cursor.getLong(4)));
			acc.setBalance(cursor.getLong(5));
			try {
				Date date = _format.parse(cursor.getString(6));
				acc.setDateTime(date);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			acc.setDescription(cursor.getString(7));
			accs.add(acc);
			cursor.moveToNext();
			//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		}
		cursor.close();
		return accs;
	}
	
	public ArrayList<String> getAllAccountName(){
		ArrayList<String> accs = new ArrayList<String>();		
		Cursor cursor = _database.query(MoneyDataHelper.ACCOUNT_TABLE, 
				new String[]{"_name"},null, null, null,null, null);
		cursor.moveToFirst();
		
		while(!cursor.isAfterLast()){
			accs.add(cursor.getString(0));
			cursor.moveToNext();
			//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		}
		cursor.close();
		return accs;
	}
	
	private AccountType getAccountTypeFromList(ArrayList<AccountType> accs, long id){
		
		for(AccountType ac: accs){
			if(ac.getId() == id)
				return ac;
		}
		AccountType acc = getAccountTypeById(id);
		return acc;
	}
	
	private CurrencyUnit getCurrencyUnitFromList(ArrayList<CurrencyUnit> units, int id){
		
		for(CurrencyUnit u: units){
			if(u.getId() == id)
				return u;
		}
		CurrencyUnit unit = getCurrencyUnitById(id);
		return unit;
	}
	/*==================================================================================*/
	
	public long addTransaction(Transaction trans){
		Account acc = trans.getAccount();
		
		ContentValues values = new ContentValues();
		values.put("_cat", trans.getCategory().getId());
		values.put("_account", trans.getAccount().getId());
		values.put("_method", trans.getPaymentMethod().getId());
		values.put("_type", trans.getType());
		values.put("_amount", trans.getAmount());
		values.put("_status", "0");
		values.put("_imagePath", trans.getImage());
		values.put("_datetime", _format.format(trans.getDatetime()));
		values.put("_desc", trans.getDescription());
		values.put("_active", trans.isActive()?1:0);
		long id = _database.insert(MoneyDataHelper.TRANSACTION_TABLE, null, values);
		
		if(id>0){
			trans.setId(id);
			//Update account -- be careful, I don't make sure about persistent right now
			if(trans.getType() == 1) //income
				acc.setBalance(acc.getBalance() + trans.getAmount()); //pay attention to exchange rate
			else
				acc.setBalance(acc.getBalance() - trans.getAmount()); //pay attention to exchange rate
			updateAccount(acc);
		}
		Log.i("AccountDAO", String.format("Insert Transaction(%d, %d) into database successfully!", trans.getId(), trans.getAmount()));
		return id;
	}
	
	public boolean updateTransaction(Transaction trans){
		ContentValues values = new ContentValues();
		values.put("_cat", trans.getCategory().getId());
		values.put("_account", trans.getAccount().getId());
		values.put("_method", trans.getPaymentMethod().getId());
		values.put("_type", trans.getType());
		values.put("_amount", trans.getAmount());
		values.put("_status", "0");
		values.put("_imagePath", trans.getImage());
		values.put("_datetime", _format.format(trans.getDatetime()));
		values.put("_desc", trans.getDescription());
		values.put("_active", trans.isActive()?1:0);
		long result = _database.update(MoneyDataHelper.TRANSACTION_TABLE, values, "_id=?", 
				new String[]{String.valueOf(trans.getId())});
		return result>=1?true:false;
		
		//Update account -- be careful, I don't make sure about persistent right now
//		if(trans.getType() == 1) //income
//			acc.setBalance(acc.getBalance() + trans.getAmount()); //pay attention to exchange rate
//		else
//			acc.setBalance(acc.getBalance() - trans.getAmount()); //pay attention to exchange rate
//		updateAccount(acc);
		
		//Log.i(TAG, String.format("Insert AccountType(%d, %s) into database successfully!", cat.getId(), cat.getName()));
	}
	
	public Transaction getTransactionById(long id){
		Transaction tran;
		Cursor cursor = _database.query(MoneyDataHelper.TRANSACTION_TABLE,
				new String[]{"_id", "_account","_method", "_cat", "_type", "_imagePath",
				"_datetime", "_amount", "_desc"}, 
				"_id=?", new String[]{String.valueOf(id)}, 
				null, null, null);
		cursor.moveToFirst();
		
		tran = new Transaction();
		tran.setId(cursor.getInt(0));
		Account acc = getAccountById(cursor.getLong(1));
		tran.setAccount(acc);
		Category cat = getCategoryById(cursor.getLong(2));
		tran.setCategory(cat);
		tran.setType(cursor.getInt(3));
		try{
			tran.setDatetime(_format.parse(cursor.getString(4)));
		}catch(ParseException e1){
			Log.e("AccountDAO", e1.getMessage());
		}
		tran.setAmount(cursor.getLong(5));
		tran.setImage(cursor.getString(6));
		tran.setDescription(cursor.getString(7));
		//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", acc.getId(), acc.getName()));
		cursor.close();
		return tran;
	}
	
	public ArrayList<Transaction> getAllTransactionByAccount(Account acc){
		ArrayList<Transaction> trans = new ArrayList<Transaction>();
		Transaction tran;
		Cursor cursor = _database.query(MoneyDataHelper.TRANSACTION_TABLE,
				new String[]{"_id", "_type", "_cat", "_method","_amount",
				"_status", "_datetime", "_imagePath","_desc", "_active"},
				"_account=? and _active=1", new String[]{String.valueOf(acc.getId())}, 
				null, null, "_datetime desc");
		
		cursor.moveToFirst();
		List<Category> cats = new ArrayList<Category>();
		
		while(!cursor.isAfterLast()){
			tran = new Transaction();
			tran.setId(cursor.getLong(0));
			tran.setType(cursor.getInt(1));
			Category cat = getCategoryFromList(cats, cursor.getLong(2));
			tran.setCategory(cat);
			tran.setAccount(acc);
			tran.setPaymentMethod(getPaymentMethodById(cursor.getLong(3)));
			tran.setAmount(cursor.getLong(4));
			tran.setStatus(cursor.getInt(5));
			try{
				tran.setDatetime(_format.parse(cursor.getString(6)));
			}catch(ParseException e1){
				Log.e("AccountDAO", e1.getMessage());
			}
			
			tran.setImage(cursor.getString(7));
			tran.setDescription(cursor.getString(8));
			tran.setActive(cursor.getInt(9)==1?true:false);
			trans.add(tran);
			cursor.moveToNext();
			//Log.i("AccountDAO", String.format("Get trans(%d) from database successfully!", tran.getId()));
		}
		cursor.close();
		return trans;
	}
	
	public ArrayList<Transaction> getAllTransactions(){
		ArrayList<Transaction> trans = new ArrayList<Transaction>();
		Transaction tran;
		Cursor cursor = _database.query(MoneyDataHelper.TRANSACTION_TABLE,
				new String[]{"_id", "_account", "_cat", "_type", "_datetime", 
					"_amount", "_imagePath","_desc"},
				null, null, null, null, null);
		cursor.moveToFirst();
		
		ArrayList<Account> accs = new ArrayList<Account>();
		ArrayList<Category> cats = new ArrayList<Category>();
		
		while(!cursor.isAfterLast()){
			tran = new Transaction();
			tran.setId(cursor.getLong(0));
			Account acc = getAccountFromList(accs, cursor.getLong(1));
			tran.setAccount(acc);
			
			Category cat = getCategoryFromList(cats, cursor.getLong(2));
			tran.setCategory(cat);
			
			tran.setType(cursor.getInt(3));
			
			try{
				tran.setDatetime(_format.parse(cursor.getString(4)));
			}catch(ParseException e1){
				Log.e("AccountDAO", e1.getMessage());
			}
			
			tran.setAmount(cursor.getLong(5));
			tran.setImage(cursor.getString(6));
			tran.setDescription(cursor.getString(7));
			trans.add(tran);
			cursor.moveToNext();
			//Log.i(TAG, String.format("Get account(%d, %s) from database successfully!", cat.getId(), cat.getName()));
		}
		cursor.close();
		return trans;
	}
}
